
package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.operations.process.forms.FinishedGoodsForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import org.apache.struts.util.LabelValueBean;


public class FinishedGoodsDAO {

    public static FinishedGoodsForm loadFinishedGoodsData (int processStepId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        FinishedGoodsForm finishedGoodsForm = null;
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.finishedgoods.loadFinishedGoodsData"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();
    
            if (rset.next()) {
                finishedGoodsForm = new FinishedGoodsForm (rset.getInt ("IST_Min_Level")+"",
                                               rset.getInt ("IST_Max_Level")+"",
                                               rset.getInt ("IST_Reorder_Level")+"",
                                               rset.getString ("IST_Manufacturing_Part_Number"),
                                               rset.getString ("IST_Marketing_Part_Number"),
                                               rset.getString ("Part_Number"),
                                               rset.getInt ("P_Parent_Product_ID"),
                                               rset.getString ("PS_Type"),
                                               rset.getString ("PS_Comments"),
                                               rset.getInt ("previousStep_ID"));
            }
            
            // added by ravikanth on 18 Sep 2003
            if( finishedGoodsForm != null )
            {
				finishedGoodsForm.setDupProductName(ProductDao.loadProductDupNames( processStepId ));
            }
			
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load finished goods data from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return finishedGoodsForm;
    }
    
    
  
    public static void storeFinishedGoodsData (DBTransaction dbTransaction, int processStepId, FinishedGoodsForm fgForm) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.finishedgoods.insertNew"));

            pstmt.setInt (1, processStepId);
            pstmt.setInt (2, fgForm.getMinLevelAsInt());
            pstmt.setInt (3, fgForm.getMaxLevelAsInt());
            pstmt.setInt (4, fgForm.getReorderLevelAsInt());
            pstmt.setString (5, fgForm.getMftPartNumber());
            pstmt.setString (6, fgForm.getMktPartNumber());

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            /*
            // Save locations
            String[] locations = fgForm.getLocations();

            if (locations != null) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.finishedgoods.insertLocations"));

                for (int i=0; i<locations.length; i++) {

                    pstmt.setInt(1, processStepId);
                    pstmt.setInt(2, Integer.parseInt(locations[i]));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
            */
            
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store FinishedGoods data", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

	//Update Finished goods Data added by Raj Vallamsetla

	public static void updateFinishedGoodsData (DBTransaction dbTransaction, int processStepId, FinishedGoodsForm fgForm) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.finishedgoods.update"));

            pstmt.setInt (1, fgForm.getMinLevelAsInt());
            pstmt.setInt (2, fgForm.getMaxLevelAsInt());
            pstmt.setInt (3, fgForm.getReorderLevelAsInt());
            pstmt.setString (4, fgForm.getMftPartNumber());
            pstmt.setString (5, fgForm.getMktPartNumber());
			pstmt.setInt (6, processStepId);

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
              
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update FinishedGoods data", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    




    
    /**
     * Load all finished goods products
     * Please contact operations team before making any changes in this method or the stored proc that is being 
     * called in this method as this is being used by Ops dept also from now on. Bhaskar on 29 March 2004
     * @return productList
     */
    public static ArrayList loadAllFinishedGoods() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();
                
        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadFinishedGoods"));

           rs = pstmt.executeQuery();

           
           FinishedGoods finishedGoods = null;
           while(rs.next()) {
        	finishedGoods = new FinishedGoods(
                            rs.getInt("ProcessStep_ID"),
                            rs.getString("IST_Manufacturing_Part_Number"),
                            rs.getString("IST_Marketing_Part_Number"),
							rs.getInt("Product_ID"),
							rs.getString("P_Type"),
                            rs.getString("P_Comments"));

                productList.add(finishedGoods);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;        
    }

    /**
     * This is method loads all the finished goods including un-approved finished goods
     *
     * @return productList
     * @throws ScalarException
     */
    public static ArrayList loadFinishedGoods() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoods"));

           rs = pstmt.executeQuery();


           FinishedGoods finishedGoods = null;
           while(rs.next()) {
            finishedGoods = new FinishedGoods(
                            rs.getInt("ProcessStep_ID"),
                            rs.getString("IST_Manufacturing_Part_Number"),
                            rs.getString("IST_Marketing_Part_Number"),
                            rs.getInt("Product_ID"),
                            rs.getString("P_Type"),
                            rs.getString("P_Comments"));

                productList.add(finishedGoods);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return productList;
    }


    /**
     * Load all parent products by type(chips, boards and kits)
     * @return productList
     */
    public static ArrayList loadAllParentPartNumbersByType() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadParentPartNumbersByType"));

           rs = pstmt.executeQuery();


           FinishedGoods finishedGoods = null;
           while(rs.next()) {
        	finishedGoods = new FinishedGoods(
                    rs.getInt("ProcessStep_ID") ,
                    rs.getString("IST_Manufacturing_Part_Number"),
                    null,
                    -1,
                    rs.getString("P_Type"),
                    null );

                productList.add(finishedGoods);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load ParentPart Numbers Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;
    }

   //Added by Sarada on 2nd Jan 2004 to get customer Part numbers corresponding to customer id
    public static ArrayList loadAllFinishedGoodsWithCustomerPartNos(int customerId) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList productList = new ArrayList();
         int odmId = 0;

         try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoodsWithCustomerPartNos"));
            pstmt.setInt( 1 , customerId );
            rs = pstmt.executeQuery();


            FinishedGoods finishedGoods = null;
            while(rs.next()) {
                 finishedGoods = new FinishedGoods(
                                 rs.getInt("ProcessStep_ID"),
                                 rs.getString("IST_Manufacturing_Part_Number"),
                                 rs.getString("IST_Marketing_Part_Number"),
                                 rs.getInt("Product_ID"),
                                 rs.getString("P_Type"),
                                 rs.getString("P_Comments"),
                                 rs.getString("Customer_PartNumber"));
                 finishedGoods.setLabelNumber( rs.getString("Label_Number") );
                 odmId = rs.getInt("ODM_ID");
                 finishedGoods.setOdmId( odmId == 0 ? -1 : odmId );
                 productList.add(finishedGoods);
            }
         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }

         return productList;
     }



	/* added by vasanth 08/17/2003
	 * return the products status whether approved or not
	 * @param processStepId
	 * return int 0/1
	*/
	
	 public static boolean isApprovedFinishedGoods (int processStepId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isApproved= false;
        
                
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.finishedgoods.isApprovedFinishedGoods"));
            pstmt.setInt (1, processStepId);
            
            rs= pstmt.executeQuery();
			if (rs.next())
				isApproved=true;
		             
        }

		catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load the status of the finished good", sqle);
        }
        finally {
            
			if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
		
        return isApproved;
    }
	
	/*
	*	Vasanth Tatta 10/13/2003
	*	@params:
	*	Returns: Arraylist of finished goods without board and kit
	*/

	 public static ArrayList loadAllFinishedGoodsWithoutBoardAndKits() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();
                
        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadFinishedGoods"));

           rs = pstmt.executeQuery();

           
           FinishedGoods finishedGoods = null;
           while(rs.next()) {
        	finishedGoods = new FinishedGoods(
                            rs.getInt("ProcessStep_ID"),
                            rs.getString("IST_Manufacturing_Part_Number"),
                            rs.getString("IST_Marketing_Part_Number"),
							rs.getInt("Product_ID"),
							rs.getString("P_Type"),
                            rs.getString("P_Comments"));
				if (rs.getString("P_Type")==null && rs.getString("ProcessStep_ID")!=null){
					productList.add(finishedGoods);
				}
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;        
    }

    /**
     * This method loads all finished goods without board and kit including un-uproved
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadFinishedGoodsWithoutBoardAndKits() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoods"));

           rs = pstmt.executeQuery();


           FinishedGoods finishedGoods = null;
           while(rs.next()) {
            finishedGoods = new FinishedGoods(
                            rs.getInt("ProcessStep_ID"),
                            rs.getString("IST_Manufacturing_Part_Number"),
                            rs.getString("IST_Marketing_Part_Number"),
                            rs.getInt("Product_ID"),
                            rs.getString("P_Type"),
                            rs.getString("P_Comments"));
                if (rs.getString("P_Type")==null && rs.getString("ProcessStep_ID")!=null){
                    productList.add(finishedGoods);
                }
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;
    }


    public static ArrayList loadAllFinishedGoodsWithEndUserPartNos(int endUserId) throws ScalarException
    {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList productList = new ArrayList();

         try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoodsWithEndUserPartNos"));
            pstmt.setInt( 1 , endUserId );
            rs = pstmt.executeQuery();


            FinishedGoods finishedGoods = null;
            while(rs.next()) {
             finishedGoods = new FinishedGoods(
                             rs.getInt("ProcessStep_ID"),
                             rs.getString("IST_Manufacturing_Part_Number"),
                             rs.getString("IST_Marketing_Part_Number"),
                             rs.getInt("Product_ID"),
                             rs.getString("P_Type"),
                             rs.getString("P_Comments"),
                     rs.getString("Customer_PartNumber"));
              finishedGoods.setLabelNumber(rs.getString("Label_Number") );

                 productList.add(finishedGoods);
            }
         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }

         return productList;
     }

    public static String getPartNumber( DBTransaction dbTransaction, int productId ) throws ScalarException {

        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rs    = null;

        boolean success    = false;
        String  partNumber = null;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = ?" );
            pstmt.setInt( 1, productId );
            rs = pstmt.executeQuery();

            while ( rs.next() ) {
                partNumber = rs.getString("PART_NUMBER");
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Part Number from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return partNumber;
    }

    public static String getPartNumberByProductId( DBTransaction dbTransaction, int productId ) throws ScalarException {

        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rs    = null;

        boolean success    = false;
        String  partNumber = null;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PRODUCT_ID = ?" );
            pstmt.setInt( 1, productId );
            rs = pstmt.executeQuery();

            while ( rs.next() ) {
                partNumber = rs.getString("PART_NUMBER");
            }
            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Part Number from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return partNumber;
    }
    public static ArrayList loadAllFGIProducts() throws ScalarException
    {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs   = null;
        ArrayList fgiPartsList = new ArrayList();

        try
        {
            con = DataSource.getConnection();
            stmt = con.prepareStatement( "SELECT A.PROCESSSTEP_ID,PART_NUMBER FROM PRODUCT A,PROCESS_STEP B WHERE A.PROCESSSTEP_ID = B.PROCESSSTEP_ID AND B.PS_TYPE LIKE 'Blank FGI' AND B.PS_TYPE_NAME LIKE 'Class Store' ORDER BY PART_NUMBER" );
            rs = stmt.executeQuery();
            while( rs.next())
            {
                fgiPartsList.add( new LabelValueBean(  rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID") ));
            }
        }
        catch( Exception e)
        {
            throw new ScalarException("Exception while fetching Fgi parts !");
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return fgiPartsList;
    }

}